﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CinemaBookingProject.DBLayer;
using System.Threading;
using System.Configuration;

public class DBEmployee
{
    static string connString = ConfigurationManager.ConnectionStrings["Balder"].ConnectionString;
    static internal SqlConnection Connection = new SqlConnection(connString); // new SqlConnection("Data Source=balder.ucn.dk;Initial Catalog=dmaa0912_E8;Persist Security Info=True;User ID=dmaa0912_E8;Password=MaaGodt");
    private static object obj = new object();
    private string select = "SELECT ID, PhoneNO, FirstName, LastName, IsActive, IsAdmin FROM Employee";
    private string delete = "DELETE FROM Employee";
    private string update = "UPDATE Employee SET IsActive = @IsActive, IsAdmin = @IsAdmin";

    /// <summary>
    /// A private method used to add "WHERE" in the statement.
    /// </summary>
    /// <param name="query">Insert the old query string</param>
    /// <param name="parameter">The statement after "WHERE"</param>
    /// <returns>Returns the query string</returns>
    private string WhereAdder(string query, string parameter)
    {
        return query + " WHERE " + parameter;
    }

    /// <summary>
    /// A read method to find a employee either by id, first name or last name
    /// </summary>
    /// <param name=""></param>
    /// <returns>A list of employees if there are more in the database</returns>
    public List<Employee> Read(string fName = null, string lName = null, string phoneNo = null)
    {
        SqlDataReader rdr = null;
        List<Employee> employees = new List<Employee>();
        try
        {
            SqlCommand cmd;
            Connection.Open();
            cmd = new SqlCommand(WhereAdder(select, "FirstName = @FirstName OR LastName = @LastName OR PhoneNO = @PhoneNO"), Connection);
            
            cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = fName;
            cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = lName;
            cmd.Parameters.Add("@PhoneNO", SqlDbType.VarChar).Value = phoneNo;

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                Employee emp = new Employee((string)rdr["PhoneNO"], (string)rdr["LastName"], (string)rdr["FirstName"], (bool)rdr["IsAdmin"], (int)rdr["ID"],(bool)rdr["IsActive"]);
                employees.Add(emp);
            }
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }
            if (Connection != null)
            {
                Connection.Close();
            }
        }

        return employees;
    }
    
    /// <summary>
    /// Insert method for employee
    /// </summary>
    /// <param name="fname">First name</param>
    /// <param name="lname">Last name</param>
    /// <param name="isAdmin">If the employee has administration rights</param>
    /// <param name="employeed">If the employee has been fired or some thing else.</param>
    /// <returns>If it goes well it returns +1, if there is an SQL Exception returns -1</returns>
    public int Insert(Employee emp)
    {
        int result = 0;
        try
        {
            Monitor.Enter(obj);

            string insert = "INSERT INTO Employee VALUES (@PhoneNO, @FirstName, @LastName, @IsActive, @IsAdmin) ";
            Connection.Open();
            SqlCommand cmd = new SqlCommand(insert, Connection);
            cmd.Parameters.Add("@PhoneNO", SqlDbType.VarChar).Value = emp.PhoneNo;
            cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = emp.fName;
            cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = emp.lName;
            cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = emp.IsActive;
            cmd.Parameters.Add("@IsAdmin", SqlDbType.Bit).Value = emp.IsAdmin;
            result = cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            result = -1;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (Connection != null)
            {
                Connection.Close();
            }
            Monitor.Exit(obj);
        }

        return result;
    }

    /// <summary>
    /// Delete method for Employee
    /// </summary>
    /// <param name="phoneNo">To find the specific employee to be deltet from database.</param>
    /// <returns>If it goes well it returns +1, if there is an SQL Exception returns -1</returns>
    public int Delete(int id)
    {
        int result = 0;
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand(WhereAdder(delete, "ID = @ID"), Connection);
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
            result = cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            result = -1;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (Connection != null)
            {
                Connection.Close();
            }
        }

        return result;
    }

    /// <summary>
    /// Update if the Employee gets a promotion or gets fired.
    /// </summary>
    /// <param name="emp">Use an employee object</param>
    /// <returns>If it goes well it returns +1, if there is an SQL Exception returns -1</returns>
    public int Update(Employee emp)
    {
        int result = 0;
        try
        {
            Monitor.Enter(obj);
            Connection.Open();
            SqlCommand cmd = new SqlCommand(WhereAdder(update, "ID = @ID"), Connection);
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = emp.Id;
            cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = emp.IsActive;
            cmd.Parameters.Add("@IsAdmin", SqlDbType.Bit).Value = emp.IsAdmin;
            result = cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            result = -1;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (Connection != null)
            {
                Connection.Close();
            }
            Monitor.Exit(obj);
        }

        return result;
    }
}

